<?php
class class_estadisticos
{
	private $_db;
	private function config(){
		require_once("conexion.php");
		require_once("DataBase.php");
		$this->_db = new DataBase($SERVIDOR,$USUARIO,$PASSWORD,$BASE);
		mysql_query("SET NAMES utf8");
	} 
	function __construct()
	{
		$this->config();
	}
	function method_getObrasSociales ($params, $error) {
		$this->_db->setQuery("SELECT tabla as value, descripcion as label FROM obras_sociales");
		$this->_db->alter();
		return $this->_db->loadResult();
	}
	function method_getPlanes ($params, $error) {
		$p = $params[0];
		$this->_db->setQuery("
		(SELECT '' as value, 'TODOS' as label)
		UNION
		(
			SELECT 
			planes.cod_plan as value,
			planes.descripcion as label 
			FROM planes
			INNER JOIN obras_sociales ON obras_sociales.tabla = '" . $p->os . "'
			WHERE planes.cod_obra_social = obras_sociales.cod_obra_social
		)
		");
		$this->_db->alter();
		return $this->_db->loadResult();
	}
	function method_getConsultasPracticasCirugias($params, $error)
    {
    	$p = $params[0];
		$p->os = "os_" . $p->os;
//    	$p->fecha_null = substr($p->hasta, 4, 3) . substr($p->hasta, 0, 3) . substr($p->hasta, 7, 4);
    	$p->desde = owFunciones::DateToSQL($p->desde);
    	$p->hasta = owFunciones::DateToSQL($p->hasta); 
    	$p->fecha_null = $p->hasta;
    	 
    	$result = "";
    	$result->lineas = array();
    	$result->series = array();
    	$result->titulo = "Consultas, Practicas y Cirugias de la Obra Social: " . $p->nombre_os;
    	
    	$f1 = "";
    	$f2 = "";
    	$f3 = "";
    	$consultas = 0;
    	$practicas = 0;
    	$cirugias = 0;
    	if ($p->plan) {
    		$filtro_plan = " AND turnos.cod_plan = '" . $p->plan . "' ";
    		$result->titulo .= " con el Plan: " . $p->nombre_plan;
    	} else {
    		$filtro_plan = "";
    	}
    	
    	
    	$ban = true;
        $this->_db->setQuery("
		SELECT
		fecha as desde, 
		DATE_FORMAT(fecha, '%m/%Y') as fecha,
		SUM(" . $p->os . ".importe) as importe 
		FROM " . $p->os . "
		INNER JOIN turnos USING(cod_turno)
		INNER JOIN nomenclador USING(cod_nomenclador)
		WHERE fecha
		BETWEEN '" . $p->desde . "' AND '" . $p->hasta . "'
		AND " . $p->os . ".estado IN ('A', 'P', 'C')
		AND nomenclador.codigo IN ('420101','423007','420106')
		" . $filtro_plan . "
		GROUP BY DATE_FORMAT(fecha, '%m/%Y')
		ORDER BY desde
        ");
        $this->_db->alter();
    	if (mysql_error()) { $error->SetError(JsonRpcError_Unknown, (__FILE__ . " - " . (__LINE__ - 2) . ": " . mysql_error())); return $error; }
    	$regs = $this->_db->loadResult();
    	if (count($regs)) {
	    	$rows = array();
	    	FOREACH ($regs as $reg)
	    	{
	    		if ($ban) {
	    			$f1 = $reg->desde;
	    			$ban = false;
	    		}
	    		$row = array();
	    		$reg->fecha = substr($reg->fecha, 0, 3) . "01/" . substr($reg->fecha, -4);
	    		array_push($row, $reg->fecha);
				array_push($row, round($reg->importe, 2));
	    		array_push($rows, $row);
	    		$consultas += round($reg->importe, 2);
	    	}
	    	array_push($result->lineas, $rows);
	    	$label = "";
    		$label->label = "Consultas: $" . number_format($consultas, 2, ",", ".");
    		array_push($result->series, $label);
    	} else {
    		$f1 = $p->fecha_null;
    	}
    	
		$ban = true;
    	$this->_db->setQuery("
    	SELECT 
    	fecha as desde,
		DATE_FORMAT(fecha, '%m/%Y') as fecha, 
    	SUM(" . $p->os . ".importe) as importe
		FROM " . $p->os . "
		INNER JOIN turnos USING(cod_turno)
		INNER JOIN nomenclador USING(cod_nomenclador)
		WHERE fecha
		BETWEEN '" . $p->desde . "' AND '" . $p->hasta . "'
		AND " . $p->os . ".estado IN ('A', 'P', 'C')
		AND nomenclador.codigo NOT IN ('420101','423007','420106') 
		AND turnos.tipo != 'C'
		" . $filtro_plan . "
		GROUP BY DATE_FORMAT(fecha, '%m/%Y')
		ORDER BY desde
        ");
        $this->_db->alter();
    	if (mysql_error()) { $error->SetError(JsonRpcError_Unknown, (__FILE__ . " - " . (__LINE__ - 2) . ": " . mysql_error())); return $error; }
    	$regs = $this->_db->loadResult();
    	if (count($regs)) {
	    	$rows = array();
	    	FOREACH ($regs as $reg)
	    	{
	    		if ($ban) {
	    			$f2 = $reg->desde;
	    			$ban = false;
	    		}
	    		$row = array();
	    		$reg->fecha = substr($reg->fecha, 0, 3) . "01/" . substr($reg->fecha, -4);
	    		array_push($row, $reg->fecha);
				array_push($row, round($reg->importe, 2));
	    		array_push($rows, $row);
	    		$practicas += round($reg->importe, 2);
	    	}
			array_push($result->lineas, $rows);
			$label = "";
    		$label->label = "Practicas: $" . number_format($practicas, 2, ",", ".");
    		array_push($result->series, $label);
    	} else {
    		$f2 = $p->fecha_null;
    	}
		
		$ban = true;
    	$this->_db->setQuery("
    	SELECT 
    	fecha as desde,
		DATE_FORMAT(fecha, '%m/%Y') as fecha,
    	SUM(" . $p->os . ".importe) as importe
		FROM " . $p->os . "
		INNER JOIN turnos USING(cod_turno)
		INNER JOIN nomenclador USING(cod_nomenclador)
		WHERE fecha
		BETWEEN '" . $p->desde . "' AND '" . $p->hasta . "'
		AND " . $p->os . ".estado IN ('A', 'P', 'C')
		AND nomenclador.codigo NOT IN ('420101','423007','420106') 
		AND turnos.tipo = 'C'
		" . $filtro_plan . "
    	GROUP BY DATE_FORMAT(fecha, '%m/%Y')
		ORDER BY desde
        ");
        $this->_db->alter();
    	if (mysql_error()) { $error->SetError(JsonRpcError_Unknown, (__FILE__ . " - " . (__LINE__ - 2) . ": " . mysql_error())); return $error; }
    	$regs = $this->_db->loadResult();
    	if (count($regs)) {
	    	$rows = array();
	    	FOREACH ($regs as $reg)
	    	{
	    		if ($ban) {
	    			$f3 = $reg->desde;
	    			$ban = false;
	    		}
	    		$row = array();
	    		$reg->fecha = substr($reg->fecha, 0, 3) . "01/" . substr($reg->fecha, -4);
	    		array_push($row, $reg->fecha);
				array_push($row, round($reg->importe, 2));
	    		array_push($rows, $row);
	    		$cirugias += round($reg->importe, 2);
	    	}
			array_push($result->lineas, $rows);
			$label = "";
    		$label->label = "Cirugias: $" . number_format($cirugias, 2, ",", ".");
    		array_push($result->series, $label);
    	} else {
    		$f3 = $p->fecha_null;
    	}
    	
		$this->_db->setQuery("
		SELECT 
			IF('" . $f1 . "' < '" . $f2 . "',
				IF ('" . $f1 . "' < '" . $f3 . "',
					DATE_FORMAT(ADDDATE('" . $f1 . "', INTERVAL -1 MONTH ), '%m/01/%Y'),
					DATE_FORMAT(ADDDATE('" . $f3 . "', INTERVAL -1 MONTH ), '%m/01/%Y')),
				IF ('" . $f2 . "'<'" . $f3 . "',
					DATE_FORMAT(ADDDATE('" . $f2 . "', INTERVAL -1 MONTH ), '%m/01/%Y'),
					DATE_FORMAT(ADDDATE('" . $f3 . "', INTERVAL -1 MONTH ), '%m/01/%Y'))
			) as fecha
		");
		$regs = $this->_db->loadResult();
		$result->fecha = $regs[0]->fecha;
		
    	return $result;
    }
    
	function method_getAgrupados($params, $error)
    {
    	$p = $params[0];
		$p->os = "os_" . $p->os;
    	$p->fecha_null = substr($p->hasta, 4, 3) . substr($p->hasta, 0, 3) . substr($p->hasta, 7, 4);
    	$p->desde = owFunciones::DateToSQL($p->desde);
    	$p->hasta = owFunciones::DateToSQL($p->hasta); 
    	
    	$result = "";
    	$result->lineas = array();
    	$result->series = array();
    	$result->titulo = "Atenciones Realizadas: " . $p->nombre_os;
    	
    	$f1 = "";
    	$consultas = 0;
    	$atenciones = 0;
    	if ($p->plan) {
    		$filtro_plan = " AND turnos.cod_plan = '" . $p->plan . "' ";
    		$result->titulo .= " con el Plan: " . $p->nombre_plan;
    	} else {
    		$filtro_plan = "";
    	}
    	
    	
    	$ban = true;
        $this->_db->setQuery("
		SELECT
		fecha as desde,
		DATE_FORMAT(fecha, '%m/%Y') as fecha,
		COUNT( turnos.cod_turno ) AS cantidad,
		SUM(importe) as importe 
		FROM " . $p->os . "
		INNER JOIN turnos USING(cod_turno)
		WHERE fecha
		BETWEEN '" . $p->desde . "' AND '" . $p->hasta . "'
		AND " . $p->os . ".estado IN ('A', 'P', 'C')
		" . $filtro_plan . "
		GROUP BY DATE_FORMAT(fecha, '%m/%Y')
		ORDER BY desde
        ");
        $this->_db->alter();
    	if (mysql_error()) { $error->SetError(JsonRpcError_Unknown, (__FILE__ . " - " . (__LINE__ - 2) . ": " . mysql_error())); return $error; }
    	$regs = $this->_db->loadResult();
    	if (count($regs)) {
	    	$rows = array();
	    	FOREACH ($regs as $reg)
	    	{
	    		if ($ban) {
	    			$f1 = $reg->desde;
	    			$ban = false;
	    		}
	    		$row = array();
	    		$reg->fecha = substr($reg->fecha, 0, 3) . "01/" . substr($reg->fecha, -4);
	    		array_push($row, $reg->fecha);
				array_push($row, round($reg->importe, 2));
	    		array_push($rows, $row);
	    		$consultas += round($reg->importe, 2);
	    		$atenciones += round($reg->cantidad, 2);
	    	}
	    	array_push($result->lineas, $rows);
	    	$label = "";
    		$label->label = "(" . $atenciones . ") Atenciones: $" . number_format($consultas, 2, ",", ".");
    		array_push($result->series, $label);
    	} else {
    		$f1 = $p->fecha_null;
    	}
    	
		$this->_db->setQuery("
		SELECT DATE_FORMAT(ADDDATE('" . $f1 . "', INTERVAL -1 MONTH ), '%m/01/%Y') as fecha
		");
		$regs = $this->_db->loadResult();
		$result->fecha = $regs[0]->fecha;
		
    	return $result;
    }
}
?>